/*
This file contains the DDL for all the objects used to create the kivadata.org.

Future improvements:
1. Add more constraints to help optimizer chose better plans.

Created by Jon Heller (jonearles@yahoo.com)
*/




------------------------------------------------------------------------------------------------------------------------
--DROP EXISTING TABLES
------------------------------------------------------------------------------------------------------------------------


/*
Drop all objects and recreate them:
drop table ... purge, in order of foreign keys:


drop table partner_country purge;
drop table country purge;
drop table partner purge;
drop table region purge;


drop table loanDescription purge;
drop table payment purge;
drop table localPayment purge;
drop table scheduledPayment purge;
drop table loan_language purge;
drop table borrower purge;

drop table loan purge;


drop table lender purge;
drop table exceptions purge;
drop sequence seq_exceptions;

drop table loan_lender purge;

drop table language purge;
drop table currency purge;
*/










------------------------------------------------------------------------------------------------------------------------
--COPY DATA FROM SECOND SCHEMA
------------------------------------------------------------------------------------------------------------------------


/*

--First, run these grants on the second schema:
grant select on country to kiva;
grant select on partner to kiva;
grant select on region to kiva;
grant select on partner_country to kiva;

grant select on loan to kiva;
grant select on loanDescription to kiva;
grant select on payment to kiva;
grant select on localPayment to kiva;
grant select on scheduledPayment to kiva;
grant select on loan_language to kiva;
grant select on borrower to kiva;

grant select on lender to kiva;
grant select on exceptions to kiva;
grant select on loan_lender to kiva;

grant select on language to kiva;
grant select on currency to kiva;




--Then run these inserts:

insert into region select * from kiva2.region;
insert into country select * from kiva2.country;
insert into partner select * from kiva2.partner;
insert into partner_country select * from kiva2.partner_country;

insert into loan select * from kiva2.loan;
insert into loanDescription select * from kiva2.loanDescription;
insert into payment select * from kiva2.payment;
insert into localPayment select * from kiva2.localPayment;
insert into scheduledPayment select * from kiva2.scheduledPayment;
insert into loan_language select * from kiva2.loan_language;
insert into borrower select * from kiva2.borrower;

insert into lender select * from kiva2.lender;
insert into exceptions select * from kiva2.exceptions;
insert into loan_lender select * from kiva2.loan_lender;

insert into language select * from kiva2.language;
insert into currency select * from kiva2.currency;
*/






------------------------------------------------------------------------------------------------------------------------
--Explanation of table settings
------------------------------------------------------------------------------------------------------------------------
--pctfree 0 - The default setting "pctfree 10" sets aside 10% of the table to help with updates, but since we almost
--            never update the tables this space is wasted.



------------------------------------------------------------------------------------------------------------------------
--DATA TABLES (PARTNER, REGION, COUNTRY, PARTNER_COUNTRY)
------------------------------------------------------------------------------------------------------------------------
create table partner
(
  id number primary key,
  name varchar2(4000),
  status varchar2(100),
  rating number,
  image_id number,
  template_id number,
  start_date date,
  delinquency_rate number,
  default_rate number,
  total_amount_raised number,
  loans_posted number
);

create table region
(
  name varchar2(100) primary key,
  latitude number,
  longitude number
);

create table country
(
  name varchar2(100) primary key,
  iso_code varchar2(2), --ISO_CODE is sometimes null.  For example, Gaza.
  region varchar2(100),
  latitude number,
  longitude number,
  gdpPerCapitaPPP number,
  constraint fk_country_region foreign key (region) references region(name)
);


create table partner_country
(
  partner_id number not null,
  country varchar2(100) not null,
  constraint fk_partner_country_partner_id foreign key (partner_id) references partner(id),
  constraint fk_partner_country_country_id foreign key (country) references country(name)
);





------------------------------------------------------------------------------------------------------------------------
--DATA TABLES (LOANS)
------------------------------------------------------------------------------------------------------------------------
create table loan
(
  id number primary key,
  name varchar2(100),
  status varchar2(100),
  funded_amount number,
  paid_amount number,
  image_id number,
  template_id number,
  activity varchar2(100),
  sector varchar2(100),
  use varchar2(4000),
  country varchar2(100),
  town varchar2(100),
  --Would prefer to name these simply level/type/pairs, but there are conflicts, so prefix all with geo
  geoLevel varchar2(100),
  geoPairs varchar2(100),  
  geoType varchar2(100),
  partner_id number, --foreign key?
  disbursal_amount number,
  disbursal_currency varchar2(100),
  disbursal_date date,
  loan_amount number,
  nonpayment varchar2(100),
  currency_exchange varchar2(100),
  posted_date date,
  funded_date date,
  defaulted_date date,
  paid_date date,
  refunded_date date,
  journal_entries number,
  journal_bulk_entries number
) pctfree 0;

create table loanDescription
(
  loan_id number not null,
  language_code varchar2(2),
  text clob,
  constraint fk_loanDescription_loan_id foreign key (loan_id) references loan(id)
) pctfree 0;

create table loan_language
(
  loan_id number not null,
  code varchar2(2),
  constraint fk_loan_language_loan_id foreign key (loan_id) references loan(id)
) pctfree 0;

create table borrower
(
  loan_id number not null,
  first_name varchar2(100),
  last_name varchar2(100),
  gender varchar2(1),
  pictured number,
  constraint fk_borrower_loan foreign key (loan_id) references loan(id)
) pctfree 0;

create table localPayment
(
  loan_id number not null,
  due_date date,
  amount number,  
  constraint fk_localpayment_loan foreign key (loan_id) references loan(id)
) pctfree 0;

create table scheduledPayment
(
  loan_id number,
  due_date date,
  amount number,
  constraint fk_scheduledpayment_loan foreign key (loan_id) references loan(id)
) pctfree 0;

create table payment
(
  loan_id number not null,
  amount number,
  payment_id number,
  local_amount number,
  processed_date date,
  settlement_date date,
  --I'd use "comment" if it wasn't a reserved word.
  commentText clob,
  rounded_local_amount number,
  constraint fk_payment_loan foreign key (loan_id) references loan(id) 
) pctfree 0;











------------------------------------------------------------------------------------------------------------------------
--DATA TABLES (LENDERS)
------------------------------------------------------------------------------------------------------------------------

create table lender
(
  lender_id varchar2(100) primary key,
  name varchar2(100),
  image_id number,
  template_id number,
  whereabouts varchar2(100),
  country_code varchar2(100),
  --uid is a reserved word
  lender_uid varchar2(100),
  member_since date,
  personal_url varchar2(4000),
  occupation varchar2(4000),
  loan_because clob,
  occupational_info clob,
  loan_count number,
  invitee_count number
) pctfree 0;




create table exceptions
(
  id number primary key,
  programName varchar2(100),
  customData varchar2(4000),
  error_stack  varchar2(4000),
  error_backtrace  varchar2(4000),
  errorDate date,
  moreDataClob clob,
  moreDataBlob blob
) pctfree 0;
create sequence seq_exceptions start with 1;


--These tables aren't needed anymore because of bulk data.
/*

--Is this table unnecessary now?
create table lastPageLoaded
(
  dataLoadType varchar2(100) not null,
  pageNumber number not null,
  dateLoaded date not null
);
insert into lastPageLoaded values ('LENDERSEARCH', 1, sysdate);


create table lenderSearch
(
  lender_id varchar2(100) primary key,
  name varchar2(100),
  whereabouts varchar2(100),
  country_code varchar2(100),
  lender_uid varchar2(100)
);
*/




------------------------------------------------------------------------------------------------------------------------
--DATA TABLES (RELATIONSHIPS)
------------------------------------------------------------------------------------------------------------------------
--Foreign keys might be a bad idea here, since lenders can change their id later.
create table loan_lender
(
  loan_id number not null
  --Can't use not null here, there are actually some lending actions without a lender_id.
  ,lender_id varchar2(100)
  --lenderOrder is based on the way the lenders are displayed in the API, which seems to always be sorted, but
  --I'm not sure if it's gaurenteed.
  ,lenderOrder number
  --Constraints on this table just don't work, there's an exception to every rule.
  --,constraint fk_loan_lender_loan_id foreign key (loan_id) references loan(id)
  --,constraint fk_loan_lender_lender_id foreign key (lender_id) references lender(lender_id)
) pctfree 0;


--alter table loan_lender drop constraint fk_loan_lender_loan_id;











------------------------------------------------------------------------------------------------------------------------
--LOOKUP TABLES
------------------------------------------------------------------------------------------------------------------------



--TODO: Create country fact table
  --based on CIA factbook
--  gdpPerCapitaNominal number,
--  gdpPerCapitaPPP number
--);

--alter table country add (gdpPerCapitaNominal number);
--alter table country add (gdpPerCapitaPPP number);

create table currency
(
  code varchar2(3) primary key,
  name varchar2(150)
);

create table language
(
  code varchar2(2),
  name varchar2(100),
  constraint pk_language primary key (code, name)
);




/*
create table GDPNominalPerCapita2008
(
  country varchar2(100),
  imfRank number,
  imfValue number,
  worldBankRank number,
  worldBankValue number,
  ciaRank number,
  ciaValue number
);
*/











------------------------------------------------------------------------------------------------------------------------
--MATERIALIZED VIEWS
------------------------------------------------------------------------------------------------------------------------

--List of months, starting with the first posted date, up to the month before the last month with data.
--The list of months is used frequently, so store it as a materialized view instead of recreating the query
--each time.  Refresh the view automatically every day.
create materialized view mv_months
refresh start with trunc(sysdate) next (sysdate)+1
as
select to_char(add_months((select max(posted_date) from loan), -(level)), 'YYYYMM') yearAndMonth
  ,to_char(add_months((select max(posted_date) from loan), -level), 'YYYYQ') yearAndQuarter
  ,case when to_date(add_months((select max(posted_date) from loan), -level)) >= to_date('01-NOV-2005','DD-MON-YYYY')
    then 1 else 0 end goodData
from dual
connect by level <= months_between((select max(posted_date) from loan), (select min(posted_date) from loan))+1
order by yearAndMonth;















------------------------------------------------------------------------------------------------------------------------
--FUNCTIONS USED IN SQL
------------------------------------------------------------------------------------------------------------------------


--String aggregation

--Cursor method.  Slow, but can be useful.
create or replace function f_list(cur in sys_refcursor
  ,delimiter in varchar default ', ') return varchar is
--Create delimited list of values based on cursor.
--Cursor must only have a single value.
  type item_t is table of varchar2(4000);
  item item_t;
  stringList varchar2(32767);
begin
  fetch cur bulk collect into item;
  close cur;
  for i in 1 .. item.count loop
    stringList := stringList||delimiter||item(i);
  end loop;
  --Remove first delimiter
  return substr(stringList, nvl(length(delimiter)+1, 0));
end;
/




--Variation of the Oracle-developer.net version.  It's mostly the same, except I added a column to the nested table
--that will hold the rownumber used for sorting.  This is the fastest way to aggregate strings and sort.  The only
--downside is that it will create temporary system types for each query, but they should spontaneously disappear.
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

create or replace type collect_obj is object
(
  value varchar2(4000),
  rownumber number
);
/

create or replace type collect_ntt is table of collect_obj;
/


CREATE or replace FUNCTION to_string(
                nt_in        IN collect_ntt,
                delimiter_in IN VARCHAR2 DEFAULT ',',
                include_nulls in number default 1
                ) RETURN VARCHAR2 IS
   sorted_nt varchar2_ntt;
   v_idx PLS_INTEGER;
   v_str VARCHAR2(32767);
   v_dlm VARCHAR2(10);
BEGIN
  select cast(multiset(select value from table(nt_in) order by rownumber) as varchar2_ntt)
  into sorted_nt
  from dual;

  v_idx := sorted_nt.FIRST;

  if include_nulls = 1 then
    WHILE v_idx IS NOT NULL LOOP
      v_str := v_str || v_dlm || sorted_nt(v_idx);
      v_dlm := delimiter_in;
      v_idx := sorted_nt.NEXT(v_idx);
    END LOOP;
  else --don't include null values in string
    WHILE v_idx IS NOT NULL LOOP
      if sorted_nt(v_idx) is not null then
        v_str := v_str || v_dlm || sorted_nt(v_idx);
        v_dlm := delimiter_in;
      end if;
      v_idx := sorted_nt.NEXT(v_idx);
    END LOOP;
  end if;
  RETURN v_str;
END to_string;
/


--Wrap the to_string function and return a clob instead of a varchar2.  This doesn't give the full size of a CLOB, but
--since it keeps the varchar2 in PL/SQL it will return up to 32,767 characters before throwing an error.
create or replace function to_string_clob(nt_in in collect_ntt, delimiter_in in varchar2 default ',') return clob is
begin
  return to_clob(to_string(nt_in, delimiter_in));
end;
/






--TODO: Replace all instances of to_string with this superior version.
--The column in the collect can be ordered there instead of resorting in PL/SQL.


CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/


--String aggregation using the COLLECT function, based on code from Oracle-developer.net.
--This is the fastest way to aggregate strings, although it requires some weird syntax.
CREATE or replace FUNCTION to_string2(
                nt_in        IN varchar2_ntt,
                delimiter_in IN VARCHAR2 DEFAULT ',',
                include_nulls in number default 1
                ) RETURN VARCHAR2 IS
   v_idx PLS_INTEGER;
   v_str VARCHAR2(32767);
   v_dlm VARCHAR2(10);
BEGIN
  v_idx := nt_in.FIRST;

  if include_nulls = 1 then
    WHILE v_idx IS NOT NULL LOOP
      v_str := v_str || v_dlm || nt_in(v_idx);
      v_dlm := delimiter_in;
      v_idx := nt_in.NEXT(v_idx);
    END LOOP;
  else --don't include null values in string
    WHILE v_idx IS NOT NULL LOOP
      if nt_in(v_idx) is not null then
        v_str := v_str || v_dlm || nt_in(v_idx);
        v_dlm := delimiter_in;
      end if;
      v_idx := nt_in.NEXT(v_idx);
    END LOOP;
  end if;
  RETURN v_str;
END to_string2;
/


--Wrap the to_string2 function and return a clob instead of a varchar2.  This doesn't give the full size of a CLOB, but
--since it keeps the varchar2 in PL/SQL it will return up to 32,767 characters before throwing an error.
create or replace function to_string_clob2(nt_in in varchar2_ntt, delimiter_in in varchar2 default ',') return clob is
begin
  return to_clob(to_string2(nt_in, delimiter_in));
end;
/






--EXAMPLES:
--f_list example
/*
select region, f_list(cursor(
    select distinct country.name
    from loan, country
    where loan.country = country.name
      and country.region = regions.region
    order by country.name desc
  ), ',') countries
from
(select distinct region from country) regions
;


--to_string example
select region, to_string(cast(collect(collect_obj(name, rownumber)) as collect_ntt), '|')
from (select country.*, row_number() over (order by name) rownumber from country) country
group by region;
*/



create or replace type varchar_typeTable as table of varchar2(999);
/

--Split string
--Use: select * from table(f_splitString(cursor(select 'asdf qwer l;aksjfd qwre' from dual), ' '));
create or replace function f_splitString(p_refcursor in sys_refcursor, delimiter in varchar2) return varchar_typeTable
  pipelined as
  stringsToSplit varchar_typeTable;
  stringToSplit varchar2(4000);
  outputString varchar2(999);
  delimiterIndex number;
begin
  loop
    fetch p_refcursor bulk collect into stringsToSplit limit 100;

    for i in 1 .. stringsToSplit.count loop
      stringToSplit := stringsToSplit(i);
      loop
        exit when stringToSplit is null;
    
        delimiterIndex := instr(stringToSplit, delimiter);
      
        if delimiterIndex = 0 then
          outputString := stringToSplit;
          stringToSplit := null;
        else
          outputString := substr(stringToSplit, 1, delimiterIndex-1);
          stringToSplit := substr(stringToSplit, delimiterIndex+1);
        end if;
    
        --Only return the string if it is non-null
        if outputString is not null then
          pipe row(outputString);
        end if;
        --DEBUG:
        --dbms_output.put_line('delimiterIndex = '||delimiterIndex||', outputString = '||outputString||', stringToSplit ='||
        --  stringToSplit);
      end loop; --end loop through single string
    end loop; --end loop through 100 rows

    exit when p_refcursor%notfound;   
  end loop; --end loop through cursor
  close p_refcursor;
end;
/


--Convert GMT to Eastern Time
--Even though there are dozens of Oracle types and functions dedictated to timezone, there is really no way to
--have Oracle convert from GMT to Eastern time.  (The problem is that we don't know if it's EST or EDT.)  
create or replace function convertGMTToEastern(v_date in date) return date is
begin
  return v_date - (case extract(year from sysdate)
    when 2005 then case when sysdate between
      to_date('03-APR-2005 06', 'DD-MON-YYYY HH24') and to_date('30-OCT-2005 07', 'DD-MON-YYYY HH24') then 4 else 5 end
    when 2006 then case when sysdate between
      to_date('02-APR-2006 06', 'DD-MON-YYYY HH24') and to_date('29-OCT-2006 07', 'DD-MON-YYYY HH24') then 4 else 5 end
    when 2007 then case when sysdate between
      to_date('11-MAR-2007 06', 'DD-MON-YYYY HH24') and to_date('04-NOV-2007 07', 'DD-MON-YYYY HH24') then 4 else 5 end
    when 2008 then case when sysdate between
      to_date('09-MAR-2008 06', 'DD-MON-YYYY HH24') and to_date('02-NOV-2008 07', 'DD-MON-YYYY HH24') then 4 else 5 end
    when 2009 then case when sysdate between
      to_date('08-MAR-2009 06', 'DD-MON-YYYY HH24') and to_date('01-NOV-2009 07', 'DD-MON-YYYY HH24') then 4 else 5 end
    when 2010 then case when sysdate between
      to_date('14-MAR-2010 06', 'DD-MON-YYYY HH24') and to_date('07-NOV-2010 07', 'DD-MON-YYYY HH24') then 4 else 5 end
    --Intentionally fail if date is out of range
    else 1 / 0
  end)/24;
end convertGMTToEastern;
/





















------------------------------------------------------------------------------------------------------------------------
--kivaread account
------------------------------------------------------------------------------------------------------------------------

--Grant read-only priviliges to kivaread
begin
  --Tables
  for grants in (select 'grant select on '||table_name||' to kivaread' grantSQL from user_tables) loop
    execute immediate grants.grantSQL;
  end loop;

  --Views
  for grants in (select 'grant select on '||view_name||' to kivaread' grantSQL from user_views) loop
    execute immediate grants.grantSQL;
  end loop;

  --Materialized Views
  for grants in (select 'grant select on '||mview_name||' to kivaread' grantSQL from user_mviews) loop
    execute immediate grants.grantSQL;
  end loop;

  --Functions
  for grants in (select 'grant execute on '||object_name||' to kivaread' grantSQL from user_objects
      where object_type = 'FUNCTION') loop
    execute immediate grants.grantSQL;
  end loop;
  
  --Types
  for grants in (select 'grant execute on '||object_name||' to kivaread' grantSQL from user_objects
      where object_type = 'TYPE') loop
    execute immediate grants.grantSQL;
  end loop;

end;
/


--SYNONYM PLSQL MUST RUN AS SYSTEM.
--You don't want to run this as KIVAREAD, because KIVAREAD would need "create or replace synonym" priviliges, which
--would also allow people to drop the synonyms.
/*

--Create synonyms
begin
  --Tables (and materialized views)
  for synonyms in
  (
    select 'create or replace synonym kivaread.'||table_name||' for kiva.'||table_name synonymSQL
    from all_tables where owner = 'KIVA'
  ) loop
    execute immediate synonyms.synonymSQL;
  end loop;

  --Views
  for synonyms in
  (
    select 'create or replace synonym kivaread.'||view_name||' for kiva.'||view_name synonymSQL
    from all_views where owner = 'KIVA'
  ) loop
    execute immediate synonyms.synonymSQL;
  end loop;

  --Functions
  for synonyms in
  (
    select 'create or replace synonym kivaread.'||object_name||' for kiva.'||object_name synonymSQL
    from all_objects where object_type = 'FUNCTION' and owner = 'KIVA'
  ) loop
    execute immediate synonyms.synonymSQL;
  end loop;
  
  --Types
  for synonyms in
  (
    select 'create or replace synonym kivaread.'||object_name||' for kiva.'||object_name synonymSQL
    from all_objects where object_type = 'TYPE' and owner = 'KIVA'
  ) loop
    execute immediate synonyms.synonymSQL;
  end loop;

end;
/


*/











*/


--Purpose: Generic query procedure that prints results in a table.
--Inputs: Query - unformatted SQL query
--        pageNumber - the page number of the results
--MUST BE RUN AS SYSTEM.  Kivaread cannot create anything.
create or replace procedure kivaread.query(query in varchar2, pageNumber in number default 1) is
  cursorID integer;
  columnCount pls_integer;
  columnDescriptions dbms_sql.desc_tab;
  rowsPerPage number := 100;
  moreData boolean;
begin
  --Create cursor and describe SQL
  cursorID := dbms_sql.open_cursor;
  begin
    --There are some Oracle exploits where even a read-only account can write data by using inline views.
    --Since Express Edition is not patched, the only way to protect against this is to ensure that all queries are
    --read-only.  Wrapping every query in 'select * from (...)' should protect against DML and DDL.
    dbms_sql.parse(cursorID, 'select * from ('||query||')', dbms_sql.native);
  exception when others then
    htp.bold('Error executing query - '||dbms_utility.format_error_stack, 'id="error"');
    --DEBUG: Log exceptions
    --insert into exceptions values(seq_exceptions.nextval, 'Query', 'pageNumber = '||pageNumber||', Query = '||query
    --  ,dbms_utility.format_error_stack, dbms_utility.format_error_backtrace, sysdate, null, null);
    --commit;
    return;
  end;
  dbms_sql.describe_columns(cursorID, columnCount, columnDescriptions);
 
  --Create table, make the first row highlighted
  htp.tableOpen;
 
  --Print the column names in a highlighted row
  htp.tableRowOpen(null, null, null, null, 'bgcolor="COCOCO"');
  for i in 1 .. columnDescriptions.count loop
    htp.tableHeader(columnDescriptions(i).col_name);
  end loop;
  htp.tableRowClose;
 
  --Print all of the data.  Even though this function is called first, the
  --buttons below will appear above the results.
  owa_util.cellsPrint(cursorID, rowsPerPage, null, (to_number(pageNumber)-1) * rowsPerPage, moreData);
 
  --If the page number is greater than 1, display previous button
  if pageNumber > 1 then
    htp.formSubmit('previous', 'Previous');
  else
    htp.formSubmit('previous', 'Previous', 'disabled="true"');
  end if;
 
  --If there is more data, display the next button.  For consistency, always
  --display both buttons, just disable them as necessary.
  if moreData then
    htp.formSubmit('next', 'Next');
  else
    htp.formSubmit('next', 'Next', 'disabled="true"');
  end if;
 
  --Display the pageNumber as a hidden form item
  htp.formHidden('pageNumber', pageNumber);
 
  htp.tableClose;

  --DML should be impossible, but just in case, issue a rollback.  It can't hurt.
  rollback;
 
  --Why does this throw an error?
  --dbms_sql.close_cursor(cursorID);
end query;
/







--Configure embedded PL/SQL gateway
--Must run these commands as system:
BEGIN
  DBMS_EPG.create_dad (
    dad_name => 'kiva',
    path    => '/kiva/*');
END;
/

BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name  => 'kiva',
    attr_name  => 'default-page',
    attr_value => 'query');

  DBMS_EPG.set_dad_attribute (
    dad_name  => 'kiva',
    attr_name  => 'database-username',
    attr_value => 'KIVAREAD');
END;
/

--Without this you will be prompted for authentication
BEGIN
  DBMS_EPG.authorize_dad (
    dad_name => 'kiva',
    user    => 'KIVAREAD');
END;
/ 
















------------------------------------------------------------------------------------------------------------------------
--Web site objects
------------------------------------------------------------------------------------------------------------------------

--Used by pkg_createPages.
create or replace directory websiteDirectory as 'D:\Kiva\website';












------------------------------------------------------------------------------------------------------------------------
--ORACLE EXPORT
------------------------------------------------------------------------------------------------------------------------

--Full export
--Takes several minutes
--exp kiva file=kivaExport.dmp owner=kiva statistics=none



--Create directory (only needed once)

create or replace directory exportDirectory as 'D:\Backups and Exports\';




set serveroutput on




--Export all of the data to text files
--This takes several minutes
--TODO: Currently there are no quotation marks in the text, but if there were the
--      data_dump program doesn't have a way of escaping them.
declare
  v_directory constant varchar2(100) := 'EXPORTDIRECTORY';
  v_convert_html constant boolean := true;

  --tilde separated
  --v_delimiter constant varchar2(100) := '~';
  --v_file_extension constant varchar2(100) := 'tsv';
  --v_surrounding_char constant varchar2(100) := null;  -- '"' is useful for csv

  --CSV
  v_delimiter constant varchar2(100) := ',';
  v_file_extension constant varchar2(100) := 'csv';
  v_surrounding_char constant varchar2(100) := '"';
begin
  for tables_to_export in
  (
    select * from all_tables
    where owner = 'KIVA'
      and table_name not in ('EXCEPTIONS', 'LOANSAVAILABLEWHENPOSTED')
    order by table_name
  ) loop
    --LOAN is special because "use" column must be aliased.
    if tables_to_export.table_name = 'LOAN' then
      kiva.data_dump(
        query_in     => 'select id, name, status, funded_amount, paid_amount, image_id, template_id, activity, sector, '||
          'use loanUse, country, town, geolevel, geopairs, geotype, partner_id, disbursal_amount, disbursal_currency, '||
          'disbursal_date, loan_amount, nonpayment, currency_exchange, posted_date, funded_date, paid_date, '||
          'refunded_date, journal_entries, journal_bulk_entries from loan order by 1'
        ,file_in => tables_to_export.table_name||'.'||v_file_extension
        ,directory_in => v_directory
        ,delimiter_in => v_delimiter
        ,surrounding_char_in => v_surrounding_char
        ,convert_html_in => v_convert_html);
      null;
    else
      kiva.data_dump(
        --rownum is useful for testing
        --query_in => 'select * from (select * from '||tables_to_export.table_name||' order by 1) where rownum <= 100'
        query_in => 'select * from '||tables_to_export.table_name||' order by 1'
        ,file_in => tables_to_export.table_name||'.'||v_file_extension
        ,directory_in => v_directory
        ,delimiter_in => v_delimiter
        ,surrounding_char_in => v_surrounding_char
        ,convert_html_in => v_convert_html);
    end if;
  end loop;
end;















select loan_id, instr(newText, '&'), newText
from
(
  select loan_id
    --Replace &quot; with TWO quotes for CSV files
    ,replace(replace(replace(replace(replace( dbms_lob.substr(text, 2000, 1), 
    '&lt;', '<'), '&gt;', '>'), '&apos;', ''''), '&quot;', '""'), '&amp;', 'TODO') newText
  from loanDescription
)
where instr(newText, '&') > 0;

















--Loans that were definitely featured loans, vs. loans that may have been featured
select featured, maybeFeatured, featured / (featured+maybeFeatured) * 100 percentFeatured
from
(
  select
    count(case when loansAtDate = 1 and posted_date >= to_date('21-APR-2006', 'DD-MON-YYYY') then 1 else null end) featured
    ,count(case when loansAtDate > 1 or posted_date < to_date('21-APR-2006', 'DD-MON-YYYY') then 1 else null end) maybeFeatured
  from
  (
    select id, posted_date, to_char(posted_date, 'DD-Mon-YYYY HH24:MI:SS')
      ,count(loan.id) over (partition by loan.posted_date) loansAtDate
    from loan
  )
);


--Number of loans before Kiva had featured entrepreneurs. (170)
select count(id) from loan where posted_date <= to_date('21-APR-2006', 'DD-MON-YYYY');


















------------------------------------------------------------------------------------------------------------------------
--Loans available when posted
------------------------------------------------------------------------------------------------------------------------

--Create table to hold loans and data about the loans that were available when it was posted.
--This requires a table because the query to create this data is so slow and complex it can't be used directly
--in data_dump.
create table loansAvailableWhenPosted
(
  loan_id number primary key,
  posted_date date,
  funded_date date,
  minutesToFund number,
  minutesToFundDollar number,
  loansAvailAvgMinToFund number,
  loansAvailAvgMinToFundDollar number,
  loansAvailableCount number,
  loansAvailableList clob
) pctfree 0;




--Purpose: Load loansAvailableWhenPosted.  For each loan, find all relevant loans that were available when the loan was 
--  posted.  Insert the results into loansAvailableWhenPosted.
--WARNING: This block will take a long time.  It processes about 10 loans per second.
--TODO: Speed up with bulk collect.
--ASSUMPTIONS: There will never be more than 32,000 characters worth of loan ids available when the loan was posted.
begin
  --remove existing rows
  --Uncomment to reload all
  --execute immediate 'truncate table loansAvailableWhenPosted';

  --Loop through all the relevant rows, in order of ID  
  for allLoans in
  (
    select loan.id, loan.posted_date, loan.funded_date, loan.loan_amount
    from loan where loan.status not in ('refunded', 'fundraising') and loan.funded_date >= loan.posted_date
      --Use this line for incremental changes
      and loan.id not in (select loan_id from loansAvailableWhenPosted)
      --TEST
      --and rownum < 10
    order by loan.id
  ) loop
    --Insert all loans that were posted but not funded when the main loan was posted.
    insert into loansAvailableWhenPosted
    select loan1.id loan_id, loan1.posted_date, loan1.funded_date
      ,(loan1.funded_date - loan1.posted_date) * 24 * 60 minutesToFund
      ,(loan1.funded_date - loan1.posted_date) / loan1.loan_amount * 24 * 60 minutesToFundDollar
      ,avg(loan2.funded_date - loan2.posted_date) * 24 * 60 loansAvailAvgMinToFund
      ,avg((loan2.funded_date - loan2.posted_date) / loan2.loan_amount)*24*60 loansAvailAvgMinToFundDollar
      ,count(loan2.id) loansAvailableCount
      ,to_string_clob2(cast(collect(to_char(loan2.id) order by loan2.id) as varchar2_ntt)) loansAvailableList
    from
      (
        select loan.id, loan.posted_date, loan.funded_date, loan.loan_amount
        from loan
        where loan.status not in ('refunded', 'fundraising')
          and loan.funded_date >= loan.posted_date
          and loan.id = allLoans.id
      ) loan1
      left outer join
      (
        select loan.id, loan.posted_date, loan.funded_date, loan.loan_amount
        from loan
        where loan.status not in ('refunded', 'fundraising')
          and loan.funded_date >= loan.posted_date
      ) loan2
        on loan1.id <> loan2.id and loan1.posted_date between loan2.posted_date and loan2.funded_date
    group by loan1.id, loan1.posted_date, loan1.funded_date, loan1.loan_amount;

    --Commit every now and then so we can tell how things are going
    if mod(allLoans.id, 100) = 0 then
      commit;
    end if;
  end loop;
end;
/
























------------------------------------------------------------------------------------------------------------------------
--OBJECT QUERIES
------------------------------------------------------------------------------------------------------------------------


--Size on disk
--select bytes/(1024*1024) megaBytes, user_segments.* From user_segments;


/*Quick pseudo-backup */
--select 'create table old_'||table_name||' as select * from '||table_name||';' from user_tables;

/*
create table old_PARTNER as select * from PARTNER;
create table old_GDPNOMINALPERCAPITA2008 as select * from GDPNOMINALPERCAPITA2008;
create table old_COUNTRY as select * from COUNTRY;
create table old_LOANLANGUAGE as select * from LOANLANGUAGE;
create table old_BORROWER as select * from BORROWER;
create table old_CURRENCY as select * from CURRENCY;
create table old_LANGUAGENAME as select * from LANGUAGENAME;
create table old_LOCALPAYMENT as select * from LOCALPAYMENT;
create table old_PAYMENT as select * from PAYMENT;
create table old_SCHEDULEDPAYMENT as select * from SCHEDULEDPAYMENT;
create table old_LOAN as select * from LOAN;
*/



